package unwdmi.db;

import java.util.*;
import java.sql.*;

import unwdmi.dto.*;

/**
 * @class Database
 * @author Michel
 * Connect to database
 */
public class Database {

	private Connection connection = null;
	private Statement statement = null;
	private PreparedStatement preparedStatement = null;
    private ResultSet resultSet = null;
	
	/**********************
	 *** GLOBAL METHODS ***
	 *********************/
	
	public Database(DatabaseDTO database) {
		this.connect(database);
	};
	
	private void connect(DatabaseDTO database) {
		try {
			//Initialize DB driver
			Class.forName("com.mysql.jdbc.Driver");
			//Connect to DB
			this.connection = DriverManager.getConnection(
				"jdbc:mysql://" + database.getDBHost() +
				"/" + database.getDBName() + 
				"?user=" + database.getDBUser() +
				"&password=" + database.getDBPassword()
			);
			//Print line
			System.out.println("Server connected to DB -> " + database.getDBHost() + "/" + database.getDBName());
		} catch(Exception e) {
			e.printStackTrace();
		};
	};
	
	/********************
	 *** READ METHODS ***
	 *******************/
	
	/**
	 * Get all stations from DB
	 * @method readStations
	 * @return <ArrayList<StationsDTO>> stations
	 */
	public ArrayList<StationDTO> readStations() {
		try {
			//Stations list
			ArrayList<StationDTO> stations = new ArrayList<StationDTO>();
			//Get stations from DB
			this.statement = this.connection.createStatement();
			this.resultSet = this.statement.executeQuery("SELECT * FROM station ORDER BY stn");
		    //Loop through stations
	        while(this.resultSet.next()) {
		    	//Add station to list
		    	stations.add(new StationDTO(
		    		//Station id integer
		    		Integer.parseInt(this.resultSet.getString("stn")),
		    		//Station longitude double
		    		Double.parseDouble(this.resultSet.getString("longitude")),
		    		//Station latitude double
		    		Double.parseDouble(this.resultSet.getString("latitude")),
		    		//Station elevation double
		    		Double.parseDouble(this.resultSet.getString("elevation")),
		    		//Station name string
		    		this.resultSet.getString("name"),
		    		//Station country string
		    		this.resultSet.getString("country")
		    	));
		    };
		    System.out.println("STATIONS : " + stations.size());
		    return stations;
		} catch(Exception e) {
			e.printStackTrace();
			return null;
		}
	};
	
	/**
	 * Get measurements from DB
	 * @method readMeasurements
	 * @return <ArrayList<Measurement>> measurements
	 */
	public ArrayList<Measurement> readMeasurements() {
		try {
			//Get stations from DB
			this.statement = this.connection.createStatement();
			this.resultSet = this.statement.executeQuery("SELECT * FROM measurement ORDER BY stn");
			return this.resultSetToMeasurementList(this.resultSet);
		} catch(Exception e) {
			e.printStackTrace();
			return null;
		}
	};
	
	/**
	 * Get measurements from DB by station
	 * @method readMeasurementsByStation
	 * @return <ArrayList<Measurement>> measurements
	 */
	public ArrayList<Measurement> readMeasurementsByStation(int stn, String datetime, int limit) {
		try {
			//Get stations from DB
			this.statement = this.connection.createStatement();
			this.resultSet = this.statement.executeQuery(
				" SELECT " +
					" measurement.*, " +
					" CONCAT(date, ' ', time) AS datetime " +
				" FROM " +
					" measurement " + 
				" WHERE " +
					" stn = " + stn +
				" HAVING " +
					" datetime < \"" + datetime + "\" " +
				" ORDER BY " +
					" datetime DESC " +
				" LIMIT " + limit
			);
			//Return measurements list
			return this.resultSetToMeasurementList(this.resultSet);
		} catch(Exception e) {
			e.printStackTrace();
			return null;
		}
	};
	
	/**********************
	 *** CREATE METHODS ***
	 *********************/
	
	public void createMeasurement(Measurement measurement) {
		try {
			//INSERT QUERY
			String sql =
				" INSERT INTO " +
					" measurement " +
				" VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?) "
			;
			//Measurement attributes
			this.preparedStatement = this.connection.prepareStatement(sql);
			this.preparedStatement.setInt(1, measurement.getSTN());
			this.preparedStatement.setDate(2,measurement.getDATE());
			this.preparedStatement.setTime(3, measurement.getTIME());
			this.preparedStatement.setDouble(4,measurement.getTEMP());
			this.preparedStatement.setDouble(5, measurement.getDEWP());
			this.preparedStatement.setDouble(6,measurement.getSTP());
			this.preparedStatement.setDouble(7, measurement.getSLP());
			this.preparedStatement.setDouble(8,measurement.getVISIB());
			this.preparedStatement.setDouble(9, measurement.getWDSP());
			this.preparedStatement.setDouble(10,measurement.getPRCP());
			this.preparedStatement.setDouble(11, measurement.getSNDP());
			this.preparedStatement.setString(12,measurement.getFRSHTT());
			this.preparedStatement.setDouble(13, measurement.getCLDC());
			this.preparedStatement.setInt(14,measurement.getWNDDIR());
			this.preparedStatement.executeUpdate();
		} catch(Exception e) {
			e.printStackTrace();
		}
	};
	
	/**********************
	 *** ------ METHODS ***
	 *********************/
	
	/**
	 * Store data from resultSet in list width measurements
	 * @method resultSetToMeasurementList
	 * @param set
	 * @return <ArrayList<Measurement>> measurements
	 * @throws Exception
	 */
	private ArrayList<Measurement> resultSetToMeasurementList(ResultSet set) throws Exception {
		//Measurements list
		ArrayList<Measurement> measurements = new ArrayList<Measurement>();
		//Loop through stations
        while(set.next()) {
	    	//Add station to list
        	Measurement measurement = new Measurement(
	    		set.getInt("stn"),
	    		String.valueOf(set.getDate("date")),
	    		String.valueOf(set.getTime("time")),
	    		set.getDouble("temp"),
	    		set.getDouble("dewp"),
	    		set.getDouble("stp"),
	    		set.getDouble("slp"),
	    		set.getDouble("visib"),
	    		set.getDouble("wdsp"),
	    		set.getDouble("prcp"),
	    		set.getDouble("sndp"),
	    		set.getString("frshtt"),
	    		set.getDouble("cldc"),
	    		set.getInt("wnddir")
	    	);
        	//Print measurement
        	measurements.add(measurement);
	    };
	    //Return measurements list
	    return measurements;
	};
	
}
